﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SlamDunk.CMS.Common;

namespace SlamDunk.CMS.DAL
{
    /// <summary>
    /// ScoreLine数据访问层类（手写部分类）
    /// </summary>
    public partial class ScoreLineDal : IDAL.IScoreLineDal
    {
        #region 01.根据年份与省份条件获取记录条数
        /// <summary>
        /// 根据年份与省份条件获取记录条数
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="prov">省份</param>
        /// <returns>记录条数</returns>
        public int GetCount(short year, string prov)
        {
            string sql = "SELECT COUNT(*) FROM dbo.ScoreLines WHERE 0 = 0";

            #region 非空校验
            if (year != 0)
            {
                sql = string.Format("{0} AND [Year] = {1}", sql, year);
            }
            if (!prov.IsNullOrEmpty())
            {
                sql = string.Format("{0} AND Province LIKE '%{1}%'", sql, prov.FilterSql());
            }
            #endregion

            return (int)DbHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 02.根据年份与省份条件获取获取实体对象集合
        /// <summary>
        /// 根据年份与省份条件获取获取实体对象集合
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="year">年份</param>
        /// <param name="prov">省份</param>
        /// <returns>实体对象集合</returns>
        public List<Model.ScoreLine> GetModelList(int start, int end, short year, string prov)
        {
            List<Model.ScoreLine> list = new List<Model.ScoreLine>();
            string sql = "SELECT *, ROW_NUMBER() OVER(ORDER BY [Year] DESC, Province ASC, Major ASC) AS RowIndex FROM dbo.ScoreLines WHERE 0 = 0";

            #region 非空校验
            if (year != 0)
            {
                sql = string.Format("{0} AND [Year] = {1}", sql, year);
            }
            if (!prov.IsNullOrEmpty())
            {
                sql = string.Format("{0} AND [Province] LIKE '%{1}%'", sql, prov.FilterSql());
            }
            #endregion

            //分页处理
            sql = string.Format("SELECT * FROM ({0}) AS T WHERE T.RowIndex >= @Start AND T.RowIndex <= @End", sql);

            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@Start", start),
                new SqlParameter("@End", end)
            };

            using (SqlDataReader reader = DbHelper.ExecuteReader(sql, args))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 03.获取所有年份
        /// <summary>
        /// 获取所有年份
        /// </summary>
        /// <returns>年份集合</returns>
        public List<short> GetAllYear()
        {
            List<short> list = new List<short>();

            string sql = "SELECT DISTINCT [Year] FROM dbo.ScoreLines ORDER BY [Year] DESC";
            using (SqlDataReader reader = DbHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    list.Add((short)(reader as SqlDataReader)[0]);
                }
            }
            return list;
        }
        #endregion

        #region 04.获取所有省份
        /// <summary>
        /// 获取所有年份
        /// </summary>
        /// <returns>省份集合</returns>
        public List<string> GetAllProv()
        {
            List<string> list = new List<string>();

            string sql = "SELECT DISTINCT Province FROM dbo.ScoreLines";
            using (SqlDataReader reader = DbHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    list.Add((string)(reader as SqlDataReader)[0]);
                }
            }
            return list;
        }
        #endregion
    }
}
